# coding=utf-8
"""

"""
import pymysql

from utils.configs.db_conf import db_test, db_pre, db_beta
from utils.do_log import local_log

# @Time    :  2024-10-14 17:17:47
# @Author  :  jiangtong
# @Email   :  gingerqgyy@outlook.com
# @Project :  test_curl_convert
# @File    :  do_sql


c_log = local_log


class DoMySql:
    """
        MySQL 操作
        创建对象时, 建立连接
        销毁对象时, 断开连接

        销毁对象时 gc 的自动操作的, 我们不用显式断开连接
        缺点就是如果频繁进行数据库的断言和提值的话, 就会频繁建立和数据库的链接

        因此,
            可以创建一个全局对象,
            配置文件配置 autocommit: True,
            反复使用这个对象, 只要该对象未被 gc 回收, 就可以避免频繁创建连接
    """

    def __init__(self, venv='test', cursor_type: str = 'DICT', database=None, _id=None):
        """

            创建对象时, 建立链接并创建 cursor, 赋值给 self

        db_conf      :  配置文件的文件名, 用于区分不同数据库连接, 不用环境连接
        cursor_type  :  dict/其他 , 当为 dict 时候, cursor 会返回 dict
        database     :  库名, 如未指定, 则只连接数据库, 此时 sql 语句需要 database.table 进行查询. 如指定, 则连接该库, sql 语句只需要 table 即可
        _id          :  起名字, 如未指定, 则使用 id(self), 拿到对象的内存地址值作为 _id, 如指定, 则使用指定的内容作为 _id
        """
        if _id:
            self._id = _id
        try:
            if venv == 'test':
                _connection_info = db_test
            elif venv == 'pre':
                _connection_info = db_pre
            else:
                _connection_info = db_beta
            connection_info = {
                'host': _connection_info.get('host'),
                'port': _connection_info.get('port'),
                'user': _connection_info.get('user'),
                'password': str(_connection_info.get('pwd')),
                'read_timeout': _connection_info.get('read_timeout'),
                'autocommit': _connection_info.get('autocommit'),
            }

            if cursor_type and cursor_type.lower() == 'dict':
                _cursor_type = pymysql.cursors.DictCursor
            else:
                _cursor_type = pymysql.cursors.Cursor

            if database:
                connection_info['database'] = database

            self.connection = pymysql.connect(**connection_info)
            self.cursor = self.connection.cursor(cursor=_cursor_type)

        except AttributeError as e:
            c_log.exception('数据库连接失败，失败原因 %s', e)
        else:
            if _id:
                c_log.info(f' < id: {_id} > 连接 MySql 成功')
            else:
                c_log.info(f' < id: {id(self)} > 连接 MySql 成功')

    def __del__(self):
        """
            对象销毁时, 自动释放连接
            不用显式调用这两个 close() 方法
        """
        try:
            self.cursor.close()
            self.connection.close()
        except AttributeError as e:
            c_log.exception('数据库连接失败，失败原因 %s', e)
        else:
            if hasattr(self, '_id'):
                c_log.info(f' < id: {self._id} > 断开 MySql 成功')
            else:
                c_log.info(f' < id: {id(self)} > 断开 MySql 成功')

    def select_one(self, sql, args=None):
        """
            sql = "insert into user values(1, 'ginger', 18)"
            sql = "insert into user values( %s, %s, %s)", args = (1, 'ginger', 18)
        """

        c_log.debug(f'执行 Sql: {sql}, 参数为: {args}')
        self.cursor.execute(sql, args)
        result = self.cursor.fetchone()
        c_log.debug(f'sql结果: {result}')
        return result

    def select_all(self, sql, args=None):
        """
            sql = "insert into user values(1, 'ginger', 18)"
            sql = "insert into user values( %s, %s, %s)", args = (1, 'ginger', 18)
        """
        c_log.debug(f'执行 Sql: {sql}, 参数为: {args}')
        self.cursor.execute(sql, args)
        result = self.cursor.fetchall()
        c_log.debug(f'Sql 结果: {result}')
        return result

    def __exec(self, sql, args=None):
        try:
            c_log.debug(f'执行 Sql: {sql}, 参数为: {args}')
            affected_rows = self.cursor.execute(sql, args)
            c_log.debug(f'Sql 结果: {affected_rows}')
        except Exception as e:
            self.connection.rollback()
            raise e
        finally:
            self.connection.commit()
        return affected_rows

    def __insert(self, sql, args=None):
        affected_rows = self.__exec(sql, args)
        return affected_rows

    def __update(self, sql, args=None):
        affected_rows = self.__exec(sql, args)
        return affected_rows

    def __delete(self, sql, args=None):
        affected_rows = self.__exec(sql, args)
        return affected_rows

    def __exec_many(self, sql, args=None):
        """
            sql = "insert into user values( %s, %s, %s)",
            args = [(1, 'ginger', 18), (2, 'ginger', 18), (3, 'ginger', 18),]"
        """
        c_log.debug(f'执行 Sql: {sql}, 参数为: {args}')
        try:
            affected_rows = self.cursor.executemany(sql, args)
            c_log.debug(f'Sql 结果: {affected_rows}')
        except Exception as e:
            self.connection.rollback()
            raise e
        finally:
            self.connection.commit()
        return affected_rows


if __name__ == '__main__':
    _sql_test = "select phone,verify_content as verift_code, DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') as create_time from yl_common.sms_verify_code ORDER BY create_time desc limit 3;"
    _sql_pre = "select phone,verify_content as verift_code, DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s') as create_time from yl_common_pre.sms_verify_code ORDER BY create_time desc limit 3;"
    print(DoMySql(venv='test').select_all(_sql_test))
    print(DoMySql(venv='pre').select_all(_sql_pre))
